﻿#region
/**
 * $Date: 0001/1/1
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using BuXiaChu.OrderDishes.DAL.DataAccess;
using BuXiaChu.OrderDishes.Model;
#endregion

namespace BuXiaChu.OrderDishes.DAL
{
    public class AreaDAL
    {
        #region private const
        private const string PARAM_ID = "@Id";
        private const string PARAM_CITY_CODE = "@CityCode";
        private const string PARAM_NAME = "@Name";
        private const string PARAM_PARENT_ID = "@ParentId";
        private const string PARAM_STATUS = "@Status";
        private const string PARAM_REMARKS = "@Remarks";
        private const string PARAM_UPDATE_DATE = "@UpdateDate";
        private const string PARAM_CREATE_DATE = "@CreateDate";
        private const string PARAM_START = "@Start";
        private const string PARAM_COUNT = "@Count";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillModelFrom(SqlDataReader reader, AreaModel obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.Id = reader.IsDBNull(reader.GetOrdinal("Id")) ? 0 : reader.GetInt32(reader.GetOrdinal("Id"));
                obj.CityCode = reader.IsDBNull(reader.GetOrdinal("CityCode")) ? String.Empty : reader.GetString(reader.GetOrdinal("CityCode"));
                obj.Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? String.Empty : reader.GetString(reader.GetOrdinal("Name"));
                obj.ParentId = reader.IsDBNull(reader.GetOrdinal("ParentId")) ? 0 : reader.GetInt32(reader.GetOrdinal("ParentId"));
                obj.Status = reader.IsDBNull(reader.GetOrdinal("Status")) ? Convert.ToByte(0) : reader.GetByte(reader.GetOrdinal("Status"));
                obj.Remarks = reader.IsDBNull(reader.GetOrdinal("Remarks")) ? String.Empty : reader.GetString(reader.GetOrdinal("Remarks"));
                obj.UpdateDate = reader.IsDBNull(reader.GetOrdinal("UpdateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("UpdateDate"));
                obj.CreateDate = reader.IsDBNull(reader.GetOrdinal("CreateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("CreateDate"));
            }
            #endregion
        }

        private static SqlParameter[] GetAreaParams(AreaModel obj)
        {
            #region
            if (obj.UpdateDate == DateTime.MinValue)
            {
                obj.UpdateDate = DateTime.Now;
            }
            if (obj.CreateDate == DateTime.MinValue)
            {
                obj.CreateDate = DateTime.Now;
            }
            SqlParameter[] dbParams ={
					 SQLHelper.MakeParam(PARAM_ID, SqlDbType.Int,0,obj.Id),
					 SQLHelper.MakeParam(PARAM_CITY_CODE, SqlDbType.NVarChar, 10,obj.CityCode),					
					 SQLHelper.MakeParam(PARAM_NAME, SqlDbType.NVarChar, 50,obj.Name),					
					 SQLHelper.MakeParam(PARAM_PARENT_ID, SqlDbType.Int,0,obj.ParentId),
					 SQLHelper.MakeParam(PARAM_STATUS, SqlDbType.TinyInt,0,obj.Status),
					 SQLHelper.MakeParam(PARAM_REMARKS, SqlDbType.NVarChar, 400,obj.Remarks),					
					 SQLHelper.MakeParam(PARAM_UPDATE_DATE, SqlDbType.DateTime,0,obj.UpdateDate),
					 SQLHelper.MakeParam(PARAM_CREATE_DATE, SqlDbType.DateTime,0,obj.CreateDate)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(AreaModel obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetAreaParams(obj);

                if (obj.Id == 0)
                {
                    obj.Id = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Area_InsertUpdate", dbParams));
                    return obj.Id > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Area_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Area-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }

            return false;
            #endregion
        }

        public static AreaModel GetByAreaId(int id)
        {
            #region
            SqlDataReader reader = null;
            AreaModel obj = new AreaModel();

            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_ID, SqlDbType.Int, 0, id)
                    };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Area_GetByAreaId", dbParams);

                if (reader.Read())
                {
                    FillModelFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Area-->GetSingleObject-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static List<AreaModel> GetAllList()
        {
            #region
            SqlDataReader reader = null;
            List<AreaModel> result = new List<AreaModel>();

            try
            {
                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Area_SelectAllArea", null);

                while (reader.Read())
                {
                    AreaModel model = new AreaModel();
                    FillModelFrom(reader, model);

                    result.Add(model);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Area-->GetAllList-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return result;
            #endregion
        }

        public static List<AreaModel> GetListByParentId(int parentId)
        {
            var allList = GetAllList();
            var result = from item in allList
                         where item.ParentId == parentId
                         select item;
            return result.ToList<AreaModel>();
        }

        public static bool Delete(int id)
        {
            #region
            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_ID, SqlDbType.Int, 0, id)
                    };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Area_DeleteById", dbParams) > 0 ? true : false;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Area-->Delete-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            return false;
            #endregion
        }
    }
}
